import re

import pandas as pd
import numpy as np
from sqlalchemy import and_
from datetime import datetime
from app.services import get_base_session as get_session
from app.utils.common_func_defs import *
from app.models.data_base_models import *


# 从数据库取数
def get_data_from_mysql(session, begin_date: str, end_date: str, table_class, varlist: list = []):
    query = session.query(table_class).filter(and_(table_class.stat_time >= begin_date,
                                                   table_class.stat_time <= end_date))
    df = pd.read_sql(query.statement, session.bind)
    if varlist:
        df = df[varlist]
        return df
    else:
        return df


# 展示上传模块中对应数据库表内容
def display_uploaded0(df, table_zh_name: str):
    df = uploaded_field_corr_entozh_res(df, table_zh_name)
    df = df.fillna('nan')
    return df


######################################################## 方法定义 #######################################################


# 获取电商情况表
def get_online_complement(brand_name: str, stat_time: str, filter_brand: str):
    # 提取所需要的年和月
    stat_time = datetime.strptime(stat_time, '%Y-%m')
    # year = stat_time.year
    # month = stat_time.month

    # 读取数据
    session = get_session()
    # 计划表（对其店铺和平台去重得到要计算的df）
    query_plan = session.query(plan_month)
    df_plan = pd.read_sql(query_plan.statement, session.bind)
    df_plan = df_plan.fillna(np.nan)  # 将所有None转化为nan，避免运算时报错
    df_plan = df_plan[
        ['stat_time', 'customer_code', 'store_name', 'platform_name', 'attribute', 'brand_name', 'plan_amount']]

    if df_plan.empty:
        return None, None

    # 发货销售表
    query_sale = session.query(deliver_sale_month)
    df_sale = pd.read_sql(query_sale.statement, session.bind)
    df_sale = df_sale.fillna(np.nan)  # 将所有None转化为nan，避免运算时报错
    df_sale = df_sale[['stat_time', 'material_code', 'customer_code', 'deliver_amount', 'A_price_amount']]
    # 产品信息表
    query_product = session.query(product_information)
    df_product = pd.read_sql(query_product.statement, session.bind)
    df_product = df_product.fillna(np.nan)  # 将所有None转化为nan，避免运算时报错
    df_product = df_product[['material_code', 'brand_name', 'parent_series', 'child_series']]
    # 客户信息表
    query_custormer = session.query(customer_information)
    df_custormer = pd.read_sql(query_custormer.statement, session.bind)
    df_custormer = df_custormer.fillna(np.nan)  # 将所有None转化为nan，避免运算时报错
    df_custormer = df_custormer[['customer_code', 'store_name', 'attribute']]

    # 生成达成情况表--计划部分
    df0 = df_plan.groupby(['platform_name', 'store_name', 'stat_time', 'brand_name'], as_index=False).agg(
        {'plan_amount': np.sum}).reset_index(drop=True)  # 得到计划量

    # 将发货销售表和产品、客户表匹配，得到完整的发货销售表
    df_sale = pd.merge(df_sale, df_product, on=['material_code'], how='left')
    df_sale = pd.merge(df_sale, df_custormer, on=['customer_code'], how='left')

    # 匹配计算实际销售量
    df_sale = df_sale[['stat_time', 'material_code', 'customer_code', 'deliver_amount', 'A_price_amount',
                       'brand_name', 'parent_series', 'child_series', 'store_name', 'attribute']]
    df0 = pd.merge(df0, df_sale, on=['store_name', 'stat_time', 'brand_name'], how='left')
    df0 = df0.groupby(['platform_name', 'store_name', 'stat_time', 'brand_name', 'plan_amount', 'attribute'],
                    as_index=False).agg(
        {'deliver_amount': np.sum, 'A_price_amount': np.sum}).reset_index(drop=True)  # 得到两种计划量，下面筛选
    df0['actual_amount'] = df0.apply(
        lambda row: (row['A_price_amount'] / 10000) if row['attribute'] == '实销' else (row['deliver_amount'] / 10000),
        axis=1)  # 注意这里求和之后根据excel计算逻辑，要除以10000

    # 计算累计指标
    df0['stat_time'] = pd.to_datetime(df0['stat_time'])  # 比较稳健地将数据转化为日期格式
    df0 = df0.sort_values(by=['stat_time'], ascending=[True])

    if df0.empty:
        df0['cumulative_plan_amount'] = np.nan
    else:
        df0['cumulative_plan_amount'] = \
        df0.groupby([df0['stat_time'].dt.year, 'platform_name', 'store_name', 'brand_name'])[
            'plan_amount'].cumsum()

    if df0.empty:
        df0['cumulative_actual_amount'] = np.nan
    else:
        df0['cumulative_actual_amount'] = \
        df0.groupby([df0['stat_time'].dt.year, 'platform_name', 'store_name', 'brand_name'])[
            'actual_amount'].cumsum()

    df0.replace([np.inf, -np.inf], 0, inplace=True)  # 替换除数为0的inf

    # 根据筛选输入的要求，进行筛选和返回
    df0 = df0[(df0['stat_time'] == stat_time)]  # 先筛选月份
    if df0.empty:
        return None,None


    if filter_brand == '是':
        df0 = df0[(df0['brand_name'] == brand_name)]  # 筛选品牌

    # 下面按平台累计
    df1 = df0.groupby(['platform_name', 'stat_time'],
                    as_index=False).agg(
        {'actual_amount': np.sum, 'plan_amount': np.sum, 'cumulative_actual_amount': np.sum,
         'cumulative_plan_amount': np.sum}).reset_index(drop=True)  # 聚合累加的项
    # 表内计算：本月完成率、本月差异、月度排名
    df1['complement_rate'] = df1['actual_amount'] / df1['plan_amount']
    df1['difference_amount'] = df1['actual_amount'] - df1['plan_amount']
    df1.replace([np.inf, -np.inf], 0, inplace=True)  # 替换除数为0的inf
    df1['rank'] = df1.groupby(['stat_time'])['complement_rate'].rank(
        ascending=False, method='min')  # 添加排名（同一月份下）
    df1['cumulative_complement_rate'] = df1['cumulative_actual_amount'] / df1['cumulative_plan_amount']
    df1['cumulative_difference_amount'] = df1['cumulative_actual_amount'] - df1['cumulative_plan_amount']
    df1.replace([np.inf, -np.inf], 0, inplace=True)  # 替换除数为0的inf
    df1['cumulative_rank'] = df1.groupby(['stat_time'])['cumulative_complement_rate'].rank(
        ascending=False, method='min')  # 添加排名（同一月份下）

    # 添加小计栏：对除“零售通&社团”和“其他”两类的所有平台进行小计
    df1 = df1.sort_values(by=['stat_time', 'platform_name'], ascending=[True, True])
    df1_others = df1[(df1['platform_name'] != '零售通&社团') & (df1['platform_name'] != '其他')]
    df1_lingshoutong_and_qita = df1[(df1['platform_name'] == '零售通&社团') | (df1['platform_name'] == '其他')]
    # 生成小计行
    df1_sum_others = df1_others.groupby(['stat_time'], as_index=False).agg(
        {'actual_amount': np.sum, 'plan_amount': np.sum, 'cumulative_actual_amount': np.sum,
         'cumulative_plan_amount': np.sum}).reset_index(drop=True)
    df1_sum_others['platform_name'] = '小计（除“零售通&社团”和“其他”两类的所有平台）'
    # 表内计算：本月完成率、本月差异、月度排名
    df1_sum_others['complement_rate'] = df1_sum_others['actual_amount'] / df1_sum_others['plan_amount']
    df1_sum_others['difference_amount'] = df1_sum_others['actual_amount'] - df1_sum_others['plan_amount']
    df1_sum_others['cumulative_complement_rate'] = df1_sum_others['cumulative_actual_amount'] / df1_sum_others[
        'cumulative_plan_amount']
    df1_sum_others['cumulative_difference_amount'] = df1_sum_others['cumulative_actual_amount'] - df1_sum_others[
        'cumulative_plan_amount']
    # 组合形成添加小计行之后的df
    df1 = pd.concat([df1_others, df1_sum_others, df1_lingshoutong_and_qita], axis=0).reset_index(drop=True)  # 合并

    # 添加“品牌“列，”全部“或是某品牌名
    if filter_brand == '是':
        df1['brand_name'] = brand_name
    else:
        df1['brand_name'] = '全部'

    # 重新排序所需字段
    df1 = df1[['stat_time', 'brand_name', 'platform_name', 'plan_amount', 'actual_amount',
             'complement_rate', 'difference_amount', 'rank',
             'cumulative_plan_amount', 'cumulative_actual_amount',
             'cumulative_complement_rate', 'cumulative_difference_amount',
             'cumulative_rank']]  # 筛选所需字段（重新排序）

    df1.replace([np.inf, -np.inf], 0, inplace=True)  # 替换除数为0的inf

    # 调整日期格式
    df1['stat_time'] = df1['stat_time'].dt.strftime('%Y-%m')
    # 转中文字段名
    df1 = uploaded_field_corr_entozh_res(df1, '电商达成情况合计月统计表')
    df1 = df1.fillna('nan')
    df1['月度排名'] = df1['月度排名'].apply(lambda x: int(x) if isinstance(x, float) else x)
    df1['累计排名'] = df1['累计排名'].apply(lambda x: int(x) if isinstance(x, float) else x)
    df1["店铺名称"] = "平台累计无店铺名"

    # 聚合：按照平台和店铺，将各品牌相同的平台、店铺的值聚合在一起
    df2 = df0.groupby(['stat_time', 'platform_name', 'store_name'],
                    as_index=False).agg(
        {'actual_amount': np.sum, 'plan_amount': np.sum, 'cumulative_actual_amount': np.sum,
         'cumulative_plan_amount': np.sum}).reset_index(drop=True)  # 聚合累加的项
    # 表内计算：本月完成率、本月差异、月度排名
    df2['complement_rate'] = df2['actual_amount'] / df2['plan_amount']
    df2['difference_amount'] = df2['actual_amount'] - df2['plan_amount']
    df2.replace([np.inf, -np.inf], 0, inplace=True)  # 替换除数为0的inf
    df2['rank'] = df2.groupby(['stat_time'])['complement_rate'].rank(
        ascending=False, method='min')  # 添加排名（同一月份下）
    df2['cumulative_complement_rate'] = df2['cumulative_actual_amount'] / df2['cumulative_plan_amount']
    df2['cumulative_difference_amount'] = df2['cumulative_actual_amount'] - df2['cumulative_plan_amount']
    df2.replace([np.inf, -np.inf], 0, inplace=True)  # 替换除数为0的inf
    df2['cumulative_rank'] = df2.groupby(['stat_time'])['cumulative_complement_rate'].rank(
        ascending=False, method='min')  # 添加排名（同一月份下）

    # 添加总计和小计
    df2 = df2.sort_values(by=['stat_time', 'platform_name', 'store_name'], ascending=[True, True, True])
    df2_others = df2[(df2['platform_name'] != '零售通&社团') & (df2['platform_name'] != '其他')]
    df2_lingshoutong = df2[(df2['platform_name'] == '零售通&社团')]
    df2_qita = df2[(df2['platform_name'] == '其他')]

    # 生成总计
    df2_sum_all = df2.groupby(['stat_time'], as_index=False).agg(
        {'actual_amount': np.sum, 'plan_amount': np.sum, 'cumulative_actual_amount': np.sum,
         'cumulative_plan_amount': np.sum}).reset_index(drop=True)
    df2_sum_all['platform_name'] = '小计（除“零售通&社团”和“其他”两类的所有平台）'
    # 表内计算：本月完成率、本月差异、月度排名
    df2_sum_all['complement_rate'] = df2_sum_all['actual_amount'] / df2_sum_all['plan_amount']
    df2_sum_all['difference_amount'] = df2_sum_all['actual_amount'] - df2_sum_all['plan_amount']
    df2_sum_all['cumulative_complement_rate'] = df2_sum_all['cumulative_actual_amount'] / df2_sum_all[
        'cumulative_plan_amount']
    df2_sum_all['cumulative_difference_amount'] = df2_sum_all['cumulative_actual_amount'] - df2_sum_all[
        'cumulative_plan_amount']
    df2_sum_all['platform_name'] = '总计'
    df2_sum_all['store_name'] = '总计'

    # 生成小计行：除“零售通&社团”和”其他“之外的所有
    df2_sum_others = df2_others.groupby(['stat_time'], as_index=False).agg(
        {'actual_amount': np.sum, 'plan_amount': np.sum, 'cumulative_actual_amount': np.sum,
         'cumulative_plan_amount': np.sum}).reset_index(drop=True)
    df2_sum_others['platform_name'] = '小计（除“零售通&社团”和“其他”两类的所有平台）'
    df2_sum_others['store_name'] = '小计（除“零售通&社团”和“其他”两类的所有平台）'
    # 表内计算：本月完成率、本月差异、月度排名
    df2_sum_others['complement_rate'] = df2_sum_others['actual_amount'] / df2_sum_others['plan_amount']
    df2_sum_others['difference_amount'] = df2_sum_others['actual_amount'] - df2_sum_others['plan_amount']
    df2_sum_others['cumulative_complement_rate'] = df2_sum_others['cumulative_actual_amount'] / df2_sum_others[
        'cumulative_plan_amount']
    df2_sum_others['cumulative_difference_amount'] = df2_sum_others['cumulative_actual_amount'] - df2_sum_others[
        'cumulative_plan_amount']

    # 生成小计行：“零售通&社团”
    df2_sum_lingshoutong = df2_lingshoutong.groupby(['stat_time'], as_index=False).agg(
        {'actual_amount': np.sum, 'plan_amount': np.sum, 'cumulative_actual_amount': np.sum,
         'cumulative_plan_amount': np.sum}).reset_index(drop=True)
    df2_sum_lingshoutong['platform_name'] = '小计（“零售通&社团”）'
    df2_sum_lingshoutong['store_name'] = '小计（“零售通&社团”）'
    # 表内计算：本月完成率、本月差异、月度排名
    df2_sum_lingshoutong['complement_rate'] = df2_sum_lingshoutong['actual_amount'] / df2_sum_lingshoutong[
        'plan_amount']
    df2_sum_lingshoutong['difference_amount'] = df2_sum_lingshoutong['actual_amount'] - df2_sum_lingshoutong[
        'plan_amount']
    df2_sum_lingshoutong['cumulative_complement_rate'] = df2_sum_lingshoutong['cumulative_actual_amount'] / \
                                                        df2_sum_lingshoutong['cumulative_plan_amount']
    df2_sum_lingshoutong['cumulative_difference_amount'] = df2_sum_lingshoutong['cumulative_actual_amount'] - \
                                                          df2_sum_lingshoutong['cumulative_plan_amount']

    # 生成小计行：”其他“
    df2_sum_qita = df2_qita.groupby(['stat_time'], as_index=False).agg(
        {'actual_amount': np.sum, 'plan_amount': np.sum, 'cumulative_actual_amount': np.sum,
         'cumulative_plan_amount': np.sum}).reset_index(drop=True)
    df2_sum_qita['platform_name'] = '小计（“其他”）'
    df2_sum_qita['store_name'] = '小计（“其他”）'
    # 表内计算：本月完成率、本月差异、月度排名
    df2_sum_qita['complement_rate'] = df2_sum_qita['actual_amount'] / df2_sum_qita['plan_amount']
    df2_sum_qita['difference_amount'] = df2_sum_qita['actual_amount'] - df2_sum_qita['plan_amount']
    df2_sum_qita['cumulative_complement_rate'] = df2_sum_qita['cumulative_actual_amount'] / df2_sum_qita[
        'cumulative_plan_amount']
    df2_sum_qita['cumulative_difference_amount'] = df2_sum_qita['cumulative_actual_amount'] - df2_sum_qita[
        'cumulative_plan_amount']

    # 组合形成添加小计行之后的df
    df2 = pd.concat(
        [df2_sum_all, df2_others, df2_sum_others, df2_lingshoutong, df2_sum_lingshoutong, df2_qita, df2_sum_qita],
        axis=0).reset_index(drop=True)  # 合并

    # 添加“品牌“列，”全部“或是某品牌名
    if filter_brand == '是':
        df2['brand_name'] = brand_name
    else:
        df2['brand_name'] = '全部'

    # 重新排序所需字段
    df2 = df2[['stat_time', 'brand_name', 'platform_name', 'store_name', 'plan_amount', 'actual_amount',
             'complement_rate', 'difference_amount', 'rank',
             'cumulative_plan_amount', 'cumulative_actual_amount',
             'cumulative_complement_rate', 'cumulative_difference_amount',
             'cumulative_rank']]  # 筛选所需字段（重新排序）

    df2.replace([np.inf, -np.inf], 0, inplace=True)  # 替换除数为0的inf

    # 调整日期格式
    df2['stat_time'] = df2['stat_time'].dt.strftime('%Y-%m')

    # 转中文字段名
    df2 = uploaded_field_corr_entozh_res(df2, '电商达成情况月统计表')
    df2 = df2.fillna('nan')
    df2['月度排名'] = df2['月度排名'].apply(lambda x: int(x) if isinstance(x, float) else x)
    df2['累计排名'] = df2['累计排名'].apply(lambda x: int(x) if isinstance(x, float) else x)

    return pd.concat([df2, df1], axis=0).reset_index(drop=True), len(df2)